LeetCode-SQL-184-部门工资最高的员工

公众号：尤而小屋
作者：Peter
编辑：Peter

一、LeetCode-SQL-184-部门工资最高/N高的员工

大家好，我是Peter~

本文讲解的是LeetCode-SQL的第184题目，题目名为：部门工资最高的员工

难易程度：中等

二、题目

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。也就是说：如果部门中存在多个人同时最高，都要显示出来。

三、思路

3.1 个人思路1

个人思路：找到每个部门中的最高值，再和这个最高值进行计较；如果大于等于这个最高值，那肯定是部门最高的。

select 
    d.Name  Department
    ,e.Name  Employee
    ,e.Salary  Salary
from Employee e , Department d 
where e.DepartmentId = d.Id  --  在同一个部门中进行比计较
and e.Salary >= (select max(Salary) from Employee where DepartmentId=d.Id);  -- 找出每个部门的最高值；如果大于等于这个最高值，肯定是最高的

上面子句的作用就是找到每个部门中的薪水最高值

3.2 个人思路2

思路2是先使用窗口函数根据每个部门中每个员工的工资进行排序，从而得到每个人的排名，我们再取出需要的名次即可。

⚠️：窗口函数在Hive或者MySQL8.X才有！！！

-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT 
        d.Name,
        e.Name Employee,
        e.Salary,
        dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
      FROM Employee e
      LEFT JOIN Department d
      ON e.DepartmentId = d.Id) S
 WHERE S.number = 1

因为薪水可能有重复的，所以员工的排名可能有相同的，因此使用rank()或者dense_rank()比较适合。

通过上面的思路，我们可以变化很多花样，取出不同名次的员工：

1、取出排名前2名的员工：

-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT 
        d.Name,
        e.Name Employee,
        e.Salary,
        dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
      FROM Employee e
      LEFT JOIN Department d
      ON e.DepartmentId = d.Id) S
WHERE S.number <= 2;  -- 排名前2

2、取出第一个名或者第3名的员工

-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT 
        d.Name,
        e.Name Employee,
        e.Salary,
        dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
      FROM Employee e
      LEFT JOIN Department d
      ON e.DepartmentId = d.Id) S
 WHERE S.number = 1 or S.number =3;  -- 排名第一或者第三

3.3 官方题解

官方题解中给的思路：通过两个表的直接关联，再通过in关键词的多个字段的包含关系的使用，这种in关键词前面带有多个字段的写法还是学习啦！

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee 
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (Employee.DepartmentId , Salary) IN (  -- 两个字段同时使用
  SELECT 
   DepartmentId, -- 部门分组找出部门号和薪水的最大值
   MAX(Salary)  
  FROM Employee 
  GROUP BY DepartmentId
)

3.4 参考思路1

有位作者的思路和官方给定的思路是比较类似的：

1、根据部门分组找出最高薪水

先对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值

select   -- 根据部门找分组找到部门号和最大值
 DepartmentId,   
 max(Salary) as max_sal
from Employee
group by DepartmentId

-- 结果
DepartmentId  max_sal
1              9000
2              8000

2、把步骤1中的结果当做临时表，和原来的Employee表进行关联

select 
       a.Id,  -- 员工号
       a.Name as Employee,  -- 员工姓名
       a.Salary,  -- 员工薪水
       a.DepartmentId   --  部门id
from Employee a  -- 和原来的Employee进行关联
left join (
  select DepartmentId, max(Salary) as max_sal
  from Employee
  group by DepartmentId) b   -- b就是步骤1得到的临时表
on a.DepartmentId = b.DepartmentId and a.Salary = b.max_sal

3、从步骤2的临时结果和部门Department表相关联，取出我们想要的字段；

select 
 bb.Name as Department, 
 aa.Employee, 
 aa.Salary
from (
 select   -- 2、步骤2的结果
       a.Id,
       a.Name as Employee,
       a.Salary,
       a.DepartmentId
  from Employee a
  join (
        select  -- 1、步骤1的结果
        DepartmentId,
        max(Salary) as max_sal
        from Employee
        group by DepartmentId
      ) b on a.DepartmentId = b.DepartmentId and a.Salary = b.max_sal) aa
join Department bb on aa.DepartmentId = bb.Id;  -- 3、和部门表的关联，取出想要的字段

3.5 参考思路2

通过谓词exists的使用：部门工资最高等价于不存在

SELECT 
 D.Name AS Department,
  E1.Name AS Employee,
  E1.Salary
FROM Employee AS E1
INNER JOIN Department AS D ON E1.DepartmentId = D.Id  -- 部门分组
WHERE NOT EXISTS (SELECT *  -- 不存在E1中的薪水小于E2中的薪水，说明E1中的就是最高的
                  FROM Employee AS E2
                  WHERE E1.DepartmentId = E2.DepartmentId
                  AND E1.Salary < E2.Salary)  --  这里不同带上等号，薪水可能存在重复的情况

四、总结

SQL中查询排名问题很常见，解决方法也有很多，一般情况下窗口函数能够很好地解决。另一种常见的方法就是通过SQL多表连接的方法。

通过这篇文章不但能够工资最高的问题，还应该可以学会如何取出第N高的问题

SQL中的窗口函数，参考文章：面试必备-SQL排序和窗口函数

SQL中的连接问题，参考文章：SQL多表连接问题

推荐阅读

尤而小屋，一个温馨的小屋。小屋主人，一手代码谋求生存，一手掌勺享受生活，欢迎你的光临